﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
namespace StuMana.Services
{
    public class ConnectionController
    {
        public MySqlConnection connection;
        private string server;
        private string database;
        private string uid;
        private string password;
        //        public View myview = null;
        public ConnectionController()
        {
            server = "localhost";
            database = "iustudent_info";
            uid = "root";
            password = "";//123456";
            string connectionString;
            connectionString = "SERVER=" + server + ";" + "DATABASE=" +
                database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";charset=utf8;";
            connection = new MySqlConnection(connectionString);
        }
        public MySqlConnection getMySqlConnection()
        {
            return connection;
        }

        //open connection to database
        public bool OpenConnection()
        {
            try
            {
                connection.Open();
                return true;
            }
            catch (MySqlException ex)
            {
                //When handling errors, you can your application's response based on the error number.
                //The two most common error numbers when connecting are as follows:
                //1042: Cannot connect to server.
                //1045: Invalid user name and/or password.
                //MessageBox.Show(ex.Number.ToString());
                switch (ex.Number)
                {
                    case 1042:
                        MessageBox.Show("Cannot connect to server.  Contact administrator", "Database Error");
                        break;

                    case 1045:
                        MessageBox.Show("Invalid username/password, please try again", "Database Error");
                        break;
                }
                return false;
            }
        }
        /**
        * Hàm thực hiện các câu query:
        *       - insert, update, delete.
        */
        public void DatabaseController(string query)
        {
            //open connection
            if (this.OpenConnection() == true)
            {
                //create command and assign the query and connection from the constructor
                MySqlCommand cmd = new MySqlCommand(query, connection);

                //Execute command
                cmd.ExecuteNonQuery();

                //close connection
                this.CloseConnection();
            }
        }

        public Boolean executeNonQuery(MySqlCommand command)
        {
            try
            {
                //open connection
                if (this.OpenConnection() == true)
                {
                    ////create command and assign the query and connection from the constructor
                    //MySqlCommand cmd = new MySqlCommand(query, connection);

                    //Execute command
                    if (command != null)
                    {
                        command.ExecuteNonQuery();
                    }

                    //close connection
                    this.CloseConnection();
                    return true;
                }
                else
                    return false;

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Loi xay ra");
                return false;
            }
        }


        //Close connection
        public bool CloseConnection()
        {
            try
            {
                connection.Close();
                return true;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }

        public List<string>[] selectStringType(MySqlCommand cmd, string[] columnName, int columnDate = -1, int columnDate2 = -1)
        {
            try
            {
                //Create a list to store the result
                List<string>[] list = new List<string>[columnName.Length];
                for (int i = 0; i < columnName.Length; i++)
                {
                    list[i] = new List<string>();
                }

                //Open connection
                if (this.OpenConnection() == true)
                {
                    //Create Command
                    //MySqlCommand cmd = new MySqlCommand(query, connection);

                    //Create a data reader and Execute the command
                    MySqlDataReader dataReader = cmd.ExecuteReader();

                    //Read the data and store them in the list
                    while (dataReader.Read())
                    {
                        for (int i = 0; i < columnName.Length; i++)
                        {
                            //  if (columnName[i] == "Time")
                            if (i == columnDate || i == columnDate2)
                            {
                                MySql.Data.Types.MySqlDateTime mySQLDateTime = dataReader.GetMySqlDateTime(i);

                                try
                                {
                                    DateTime dateTime = mySQLDateTime.GetDateTime();

                                    if (i == columnDate2)
                                        list[i].Add(dateTime.ToString() + "");
                                    else
                                        list[i].Add(dateTime.ToShortDateString() + "");
                                }
                                catch (Exception ex)
                                {
                                    string msg = ex.Message;
                                    MessageBox.Show("Loi ngay sinh cua ID: " + list[0][i] + "\n" + "Co ho ten la: " + list[1][i], "DateTime Error");
                                }

                            }
                            else
                                list[i].Add(dataReader[columnName[i]] + "");
                        }
                    }

                    //close Data Reader
                    dataReader.Close();

                    //close Connection
                    this.CloseConnection();

                    //return list to be displayed
                    return list;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Loi truy van: \n" + ex.Message, "Query Error");
                return null;
            }

        }

        public List<string>[] SelectFood()
        {
            string query = "SELECT * FROM food";

            //Create a list to store the result
            List<string>[] list = new List<string>[4];
            list[0] = new List<string>();
            list[1] = new List<string>();
            list[2] = new List<string>();
            list[3] = new List<string>();


            //Open connection
            if (this.OpenConnection() == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    list[0].Add(dataReader["id"] + "");
                    list[1].Add(dataReader["name"] + "");
                    list[2].Add(dataReader["price"] + "");
                    list[3].Add(dataReader["type"] + "");
                }

                //close Data Reader
                dataReader.Close();

                //close Connection
                this.CloseConnection();

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }

    }
    
}
